package cn.dy.workatten.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import cn.dy.base.framework.db.DBConnectPool;
import cn.dy.base.framework.db.DBUtil;
import cn.dy.base.framework.db.util.DBExecuteUtil;
import cn.dy.workatten.pojo.AttendApplication;
import cn.dy.workatten.pojo.DutyScoreSummary;

public class AttendApplicationDao {

	protected static Logger logger = LoggerFactory.getLogger(AttendApplicationDao.class);

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedJdbcTemplate;

	public AttendApplicationDao() {
		this.dataSource = DBConnectPool.create().getDataSource();
		if (this.jdbcTemplate == null)
			this.jdbcTemplate = new JdbcTemplate(this.dataSource);
		if (this.namedJdbcTemplate == null) {
			this.namedJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
		}
	}
	
	/**
	 * 获得自增系列
	 * @return
	 */
	public long getUniqueId() {
		long result = 0L;
		try {
			result = DBUtil.getSequence(new JdbcTemplate(this.dataSource), "seq_bz_attend_application");
		} catch (DataAccessException e) {
			logger.error("获得考勤审批申请自动生成的ID值失败", e);
		}
		return result;
	}

	/**
	 * 新增
	 * 
	 * @return
	 */
	public int addAttendApplication(AttendApplication application) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;

		try {
			String sql = "insert into bz_attend_application(id,attendance_month,attendance_days,dept_id,sts,create_time,create_id) values(:id,:attendance_month,:attendance_days,:dept_id,:sts,:create_time,:create_id)";

			Map resultMap = new HashMap();
//			resultMap.put("id", getUniqueId());
			resultMap.put("id", application.getId());
			resultMap.put("attendance_month", application.getAttendance_month());
			resultMap.put("attendance_days", application.getAttendance_days());
			resultMap.put("dept_id", application.getDept_id());
			resultMap.put("sts", application.getSts());
			resultMap.put("create_time", application.getCreate_time());
			resultMap.put("create_id", application.getCreate_id());
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("添加考勤审批申请失败", e);
		}
		return result;
	}

	/**
	 * 修改
	 * 
	 * @param scheduleCfg
	 * @return
	 */
	public int updateAttendApplication(AttendApplication application) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = "update bz_attend_application set attendance_month=:attendance_month,attendance_days=:attendance_days,dept_id=:dept_id,sts=:sts,create_time=:create_time,create_id=:create_id where id=:id";

			Map resultMap = new HashMap();
			resultMap.put("id", Long.valueOf(application.getId()));
			resultMap.put("attendance_month", application.getAttendance_month());
			resultMap.put("attendance_days", application.getAttendance_days());
			resultMap.put("dept_id", application.getDept_id());
			resultMap.put("sts", application.getSts());
			resultMap.put("create_time", application.getCreate_time());
			resultMap.put("create_id", application.getDept_id());
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("修改考勤审批申请失败", e);
		}
		return result;
	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @return
	 */
	public int deleteAttendApplicationById(long id) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = " delete from bz_attend_application where id=:id";
			Map resultMap = new HashMap();
			resultMap.put("id", Long.valueOf(id));
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("删除考勤审批申请失败", e);
		}
		return result;
	}

	/**
	 * 根据id查询考勤审批申请信息
	 * 
	 * @param id
	 * @return
	 */
	public AttendApplication getAttendApplicationById(long id) {
		DBExecuteUtil db = new DBExecuteUtil();
		AttendApplication attendApplication = new AttendApplication();
		try {
			String sql = "select a.*, b.NAME AS deptName, c.NICKNAME AS staffName  from bz_attend_application a,ecc_contact_group b, sm_staff_info c  "
					+ " where a.id=:id and a.dept_id = b.id and a.create_id = c.id";
			
			Map parameters = new HashMap();
			parameters.put("id", Long.valueOf(id));
			attendApplication = (AttendApplication) db.queryObject(sql, parameters, AttendApplication.class);
		} catch (DataAccessException e) {
			logger.error("根据id获得考勤审批申请失败", e);
		}
		return attendApplication;
	}
	
	/**
	 * 	查询某个部门某个日期的考勤申请信息
	 * @return
	 */
	public AttendApplication getAttendApplicationByDay(long dept_id, long attendance_month, String attendance_days) {
	   DBExecuteUtil db = new DBExecuteUtil();
		AttendApplication attendApplication = new AttendApplication();
		try {
			String sql = "select a.*, b.NAME AS deptName, c.NICKNAME AS staffName  from bz_attend_application a,ecc_contact_group b, sm_staff_info c  "
					+ " where a.dept_id=:dept_id and a.attendance_month=:attendance_month and a.attendance_days=:attendance_days and a.dept_id = b.id and a.create_id = c.id";
			
			Map params = new HashMap();
			params.put("dept_id", dept_id);
		    params.put("attendance_month", attendance_month);
		    params.put("attendance_days", attendance_days);
			attendApplication = (AttendApplication) db.queryObject(sql, params, AttendApplication.class);
		} catch (DataAccessException e) {
			logger.error("查询某个部门某个日期的考勤申请信息失败", e);
		}
		return attendApplication;
	}
	
	/**
	 * 	判断某个部门某个日期的考勤是否已经存在
	 * @return
	 */
	public int isExistAttendApplication(long dept_id, long attendance_month, String attendance_days) {
	   DBExecuteUtil db = new DBExecuteUtil();
	   Map params = new HashMap();
	   String sql = "SELECT COUNT(*) FROM bz_attend_application WHERE attendance_month=:attendance_month and dept_id = :dept_id and attendance_days = :attendance_days";
	   params.put("dept_id", dept_id);
	   params.put("attendance_month", attendance_month);
	   params.put("attendance_days", attendance_days);
	   return ((Integer)db.queryObject(sql, params, Integer.class)).intValue();
	}
	
	/**
	 * 	判断某个月份的考勤是否存在未办结的考勤申请
	 * @return
	 */
	public int isExistAttendAppNotEnd(long attendance_month) {
	   DBExecuteUtil db = new DBExecuteUtil();
	   Map params = new HashMap();
	   String sql = "SELECT COUNT(*) FROM bz_attend_application WHERE attendance_month=:attendance_month and sts != 2";
	   params.put("attendance_month", attendance_month);
	   return ((Integer)db.queryObject(sql, params, Integer.class)).intValue();
	}
	
	
	/**
	 * 修改申请项目的状态
	 * 
	 * @param scheduleCfg
	 * @return
	 */
	public int updateAttendApplicationStatus(long attend_application_id, String sts) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = "update bz_attend_application set sts=:sts where id=:id";

			Map resultMap = new HashMap();
			resultMap.put("id", attend_application_id);
			resultMap.put("sts", sts);
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("修改考勤审批申请失败", e);
		}
		return result;
	}
	
	
	/**
	 * 考勤申请列表，分页查询
	 * 
	 * @param pageNum  第几页
	 * @param pageSize 一页记录数
	 * @return
	 */
	public Object queryAttendApplicationByPage(long dept_id, String sts, Date beginTime, Date endTime, int pageNum, int pageSize) {
		Map resultMap = new HashMap();
		try {
			String sql = "select a.*,c.NAME AS deptName, d.NICKNAME AS staffName from bz_attend_application a,bz_auditing_flow b, ecc_contact_group c, sm_staff_info d  where a.id = b.application_id and a.dept_id=:dept_id "
					+ " and a.dept_id = c.id and a.create_id = d.id";
			SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
			SimpleDateFormat df2 = new SimpleDateFormat("dd");
			
			
			List<AttendApplication> list = new ArrayList();
			Map<String, Object> params = new HashMap();
			params.put("dept_id", Long.valueOf(dept_id));
			if (StringUtils.isNotEmpty(sts)) {
				sql = sql + " and a.sts=:sts";
				params.put("sts", sts);
			}
			if(beginTime != null) {
				sql = sql + " and a.attendance_month >= :beginMonth and a.attendance_days >= :beginDay";
				params.put("beginMonth", Integer.parseInt(df.format(beginTime)));
				params.put("beginDay", Integer.parseInt(df2.format(beginTime)));
			}
			if(endTime != null) {
				sql = sql + " and a.attendance_month <= :endMonth and a.attendance_days <= :endDay";
				params.put("endMonth", Integer.parseInt(df.format(endTime)));
				params.put("endDay", Integer.parseInt(df2.format(endTime)));
			}
			
			int total = this.namedJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper(AttendApplication.class)).size();
			list = this.namedJdbcTemplate.query(DBUtil.getPagingSql(sql.toString()),
					DBUtil.parsePagingParam(params, pageNum, pageSize), new BeanPropertyRowMapper(AttendApplication.class));
			resultMap.put("total", Integer.valueOf(total));
			resultMap.put("list", list);

		} catch (DataAccessException e) {
			logger.error("查询考勤审批申请列表", e);
		}
		return resultMap;
	}
	
	
	/**
	 * 	考勤录入列表，分页查询
	 * 	1.具有部门考勤管理功能权限的人员进入该页面后，可以查看自己录入的所有数据。
	 *  2.平台管理员可以查看全部
	 * 
	 * @param pageNum  第几页
	 * @param pageSize 一页记录数
	 * @return
	 */
	public Object queryAddAttendApplicationByPage(long dept_id, String sts, Date beginTime, Date endTime, int pageNum, int pageSize, String user_id) {
		Map resultMap = new HashMap();
		try {
			String sql = "select a.*,c.NAME AS deptName, d.NICKNAME AS staffName from bz_attend_application a left join bz_auditing_flow b on a.id = b.application_id " + 
					"	left join ecc_contact_group c on a.dept_id = c.id left join sm_staff_info d on a.create_id = d.id where a.dept_id=:dept_id ";
			SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
			SimpleDateFormat df2 = new SimpleDateFormat("dd");
			
			
			List<AttendApplication> list = new ArrayList();
			Map<String, Object> params = new HashMap();
			params.put("dept_id", Long.valueOf(dept_id));
			if (StringUtils.isNotEmpty(sts)) {
				sql = sql + " and a.sts=:sts";
				params.put("sts", sts);
			}
			if (StringUtils.isNotEmpty(user_id)) {
				sql = sql + " and a.create_id = :user_id";
				params.put("user_id", Long.valueOf(user_id));
			}
			if(beginTime != null) {
				sql = sql + " and a.attendance_month >= :beginMonth and a.attendance_days >= :beginDay";
				params.put("beginMonth", Integer.parseInt(df.format(beginTime)));
				params.put("beginDay", Integer.parseInt(df2.format(beginTime)));
			}
			if(endTime != null) {
				sql = sql + " and a.attendance_month <= :endMonth and a.attendance_days <= :endDay";
				params.put("endMonth", Integer.parseInt(df.format(endTime)));
				params.put("endDay", Integer.parseInt(df2.format(endTime)));
			}
			
			int total = this.namedJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper(AttendApplication.class)).size();
			list = this.namedJdbcTemplate.query(DBUtil.getPagingSql(sql.toString()),
					DBUtil.parsePagingParam(params, pageNum, pageSize), new BeanPropertyRowMapper(AttendApplication.class));
			resultMap.put("total", Integer.valueOf(total));
			resultMap.put("list", list);

		} catch (DataAccessException e) {
			logger.error("查询考勤审批录入列表", e);
		}
		return resultMap;
	}
	
	
	/**
	 * 	考勤审批列表，分页查询
	 * 	1.显示提交给我的数据
	 *  2.平台管理员可以查看全部
	 * 
	 * @param pageNum  第几页
	 * @param pageSize 一页记录数
	 * @return
	 */
	public Object queryAuditAttendApplicationByPage(long dept_id, String sts, Date beginTime, Date endTime, int pageNum, int pageSize, String user_id) {
		Map resultMap = new HashMap();
		try {
			String sql = "select a.*,c.NAME AS deptName, d.NICKNAME AS staffName from bz_attend_application a left join bz_auditing_flow b on a.id = b.application_id " + 
					"	left join ecc_contact_group c on a.dept_id = c.id left join sm_staff_info d on a.create_id = d.id"
					+ " left join bz_auditing_detail e on b.cur_detail_id = e.id where a.dept_id=:dept_id ";
			SimpleDateFormat df = new SimpleDateFormat("yyyyMM");
			SimpleDateFormat df2 = new SimpleDateFormat("dd");
			
			
			List<AttendApplication> list = new ArrayList();
			Map<String, Object> params = new HashMap();
			params.put("dept_id", Long.valueOf(dept_id));
			if (StringUtils.isNotEmpty(sts)) {
				sql = sql + " and a.sts=:sts";
				params.put("sts", sts);
			}
			
			// 显示提交给我的列表
			if (StringUtils.isNotEmpty(user_id)) {
				sql = sql + " and e.audit_id = :user_id";
				params.put("user_id", Long.valueOf(user_id));
			}
			
			if(beginTime != null) {
				sql = sql + " and a.attendance_month >= :beginMonth and a.attendance_days >= :beginDay";
				params.put("beginMonth", Integer.parseInt(df.format(beginTime)));
				params.put("beginDay", Integer.parseInt(df2.format(beginTime)));
			}
			if(endTime != null) {
				sql = sql + " and a.attendance_month <= :endMonth and a.attendance_days <= :endDay";
				params.put("endMonth", Integer.parseInt(df.format(endTime)));
				params.put("endDay", Integer.parseInt(df2.format(endTime)));
			}
			
			int total = this.namedJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper(AttendApplication.class)).size();
			list = this.namedJdbcTemplate.query(DBUtil.getPagingSql(sql.toString()),
					DBUtil.parsePagingParam(params, pageNum, pageSize), new BeanPropertyRowMapper(AttendApplication.class));
			resultMap.put("total", Integer.valueOf(total));
			resultMap.put("list", list);

		} catch (DataAccessException e) {
			logger.error("查询考勤审批列表", e);
		}
		return resultMap;
	}
	
	
	
	
	
}
